//common/sql.js
module.exports = {
    users: {
        login: "select * from users where (username=? or phone=? ) and pwd=?;",
        regist: "insert into users values(0,?,?,?,?,?,'1','cust',?);",
        updatephone: "update users set phone=? where userid=?;",
        updateemail: "update users set email=? where userid=?;",
        updatepwd: "update users set pwd=? where userid=?;",
        use:"select * from users;",
        updateuser:"UPDATE users SET username=?, pwd=?, truename=?, cardid=?, phone=?, role=? WHERE userid=?" ,
        adduser:"INSERT INTO users VALUES(0,?,?,?,?,?,'1',?,?);",
        deluser:"DELETE FROM users WHERE userid = ?;"
    },
    goods: {
        catebyid: "select * from categories where cgid=?;",
        goodslist: "select * from goods where isuse='1' and cgid like ? order by gid desc limit 8;",
        goodsByid: "select *,(select count(*) from reviews where gid=?) as rvmnum from goods where isuse='1' and gid=?;",
        all: "SELECT * FROM categories INNER JOIN goods ON categories.cgid = goods.cgid WHERE goods.isuse = 1;",
        lunbo:"SELECT * FROM categories INNER JOIN goods ON categories.cgid = goods.cgid WHERE goods.isuse = 2;",
        xiaomi:"select * from goods where isuse='1' and cgid like 1 and gname like '%xiaomi%' order by gid desc limit 8",
        redmi:"select * from goods where isuse='1' and cgid like 1 and gname like '%redmi%' order by gid desc limit 8",
        book:"select * from goods where isuse='1' and cgid like 4 and gname like '%book%' order by gid desc limit 8",
        pad:"select * from goods where isuse='1' and cgid like 4 and gname like '%pad%' order by gid desc limit 8",
        dianshi:"select * from goods where isuse='1' and cgid like 2 and gname like '%电视%' order by gid desc limit 8",
        jiadian:"select * from goods where isuse='1' and cgid like 3 and gname like '%米%' order by gid desc limit 8",
        luyou:"select * from goods where isuse='1' and cgid like 9 and gname like '%路由%' order by gid desc limit 8",
         updatelunbo:"UPDATE goods SET gname=?,img=?,price=?,introduce=? WHERE gid = ?;",
        addGoods:"INSERT INTO goods VALUES(0,?,?,?,?,'1',?,?);",
        addlunbo:"INSERT INTO goods VALUES(0,?,?,?,?,'2',?,?);",
        delGoods:"DELETE FROM goods WHERE gid = ?;",
        delLunbo:"DELETE FROM goods WHERE gid = ?;",
        goodsinfo:"SELECT * FROM goods WHERE isuse = 1;",
        updateGoods:"UPDATE goods SET gname =?,img =?,cgid =?,price =?,introduce =?,addtime =? WHERE gid =?;",
    },
    reviews: {
        reviewsBygid: "select a.*,b.username,b.truename from reviews a,users b where b.userid=a.userid and a.gid=?;",
       addReview: "INSERT INTO reviews (gid, userid, contents, rvtime, img, package, speed, service, product) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);",
       userReviews:"SELECT r.rvid,r.contents,g.gid,g.gname,g.price,g.img,u.userId,u.username, DATE_FORMAT(rvtime, '%Y-%m-%d %H:%i:%s') as formatted_rvtime FROM reviews r JOIN goods g ON r.gid = g.gid JOIN users u ON r.userId = u.userId ORDER BY r.rvtime DESC;",
        updatereviews:"UPDATE reviews SET gid=?,userid=?,contents=? where rvid=?;",
        addreviews:"INSERT INTO reviews VALUES(0,?,?,?,?);",
        delreviews:"DELETE FROM reviews WHERE rvid = ?;"
    },
    carts: {
        cartsByUG: "select * from carts where userid=? and gid=?;",
        updateNum: "update carts set num=num+? where userid=? and gid=?;",
        addcart: "insert into carts values(0,?,?,?,?);",
        changeNum: "update carts set num=? where cartid=?;",
        cartList: "select a.*,b.gname,b.img from carts a,goods b where b.gid=a.gid and a.userid=?;",
        delById: "delete from carts where cartid=?;",
    },
    orders: {
        addOrder: "insert into orders values(0,?,?,?,?,'0',null,null);",
        addDetail: "insert into orderdetails values(0,?,?,?,?,'0',null,null);",
        ordersByU: "select *,DATE_FORMAT(createtime,'%Y-%m-%d %H:%i:%s') as ftime,DATE_FORMAT(arrivaltime, '%Y-%m-%d %H:%i:%s') AS fatime from orders where userid=9 order by createtime desc;",
        detailsByU: "select a.*,c.gname,c.img from orderdetails a , orders b,goods c where a.orderid=b.orderid and c.gid=a.gid and b.userid=? order by a.orderid desc;",
        order:"SELECT o.*, u.username, u.truename, DATE_FORMAT(o.createtime, '%Y-%m-%d %H:%i:%s') AS formatted_createtime FROM orders o LEFT JOIN users u ON o.userid = u.userid ORDER BY o.createtime DESC;",
        updateorder:"UPDATE orders SET ordernum = ?, userid = ?, total = ? WHERE orderid = ?",
        jiaorder:"insert into orders values(0,?,?,?,?,'0',null);",
        delorder:"DELETE FROM orders WHERE orderid = ?;"
    }
}